package cn.edu.nju.software.postgresqlmanage;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import cn.edu.nju.software.beans.StudentBean;

public class LearnersM {
	// add students to own base
	public static boolean addStudents(ArrayList<StudentBean> newStudents)
			throws SQLException {

		Connection conn = PostgreSQLConnection.getConnection();
		boolean updateOk = false;
		
		try {
			String command = "INSERT INTO learners VALUES (?,?,?,?)";
			PreparedStatement prepareState = conn.prepareStatement(command);
			
			for(StudentBean student:newStudents){
				String sid = student.getId();
				String sname = student.getName();
				String sgender = student.getGender();
				String sinstitute = student.getInstitute();
				
				prepareState.setString(1, sid);
				prepareState.setString(4, sname);
				prepareState.setString(2, sgender);
				prepareState.setString(3, sinstitute);
				
				prepareState.executeUpdate();
			}
			updateOk = true;
		}catch(Exception exc){
			updateOk = false;
		} finally {
			conn.close();
		}

		return updateOk;
	}

	// delete a student by given id
	public static boolean deleteStudent(ArrayList<String> sids)
			throws SQLException {

		Connection conn = PostgreSQLConnection.getConnection();
		boolean updateOk = false;
		
		try {
			String command = "DELETE FROM learners WHERE xuehao = ?";
			PreparedStatement prepareState = conn.prepareStatement(command);

			for (String sid : sids) {
				prepareState.setString(1, sid);

				prepareState.executeUpdate();
			}
			updateOk = true;
		} catch (Exception exc) {
			updateOk = false;
		} finally {
			conn.close();
		}

		return updateOk;
	}

	// get all students of own institute
	public static ArrayList<StudentBean> getAllStudents() throws SQLException {

		Connection conn = PostgreSQLConnection.getConnection();
		ArrayList<StudentBean> allStudents = new ArrayList<StudentBean>();
		String institute = "C";
		
		try {
			String command = "SELECT * FROM learners WHERE xueyuan = ?";
			PreparedStatement prepareState = conn.prepareStatement(command);
			prepareState.setString(1, institute);
			
			ResultSet rs = prepareState.executeQuery();
			while(rs.next()){
				String sid = rs.getString(1);
				String sname = rs.getString(4);
				String sgender = rs.getString(2);
				String sinstitute = rs.getString(3);

				StudentBean getOne = new StudentBean(sid, sname, sgender, sinstitute);
				allStudents.add(getOne);
			}
		} catch (Exception exc) {

		} finally {
			conn.close();
		}

		return allStudents;
	}

	// get students by given id list
	public static ArrayList<StudentBean> getStudents(ArrayList<String> sids)
			throws SQLException {

		Connection conn = PostgreSQLConnection.getConnection();
		ArrayList<StudentBean> targetStudents = new ArrayList<StudentBean>();
		
		try {
			String command = "SELECT * FROM learners WHERE xuehao = ?";
			PreparedStatement prepareState = conn.prepareStatement(command);
			
			for(String sid : sids){
				prepareState.setString(1, sid);
				ResultSet rs = prepareState.executeQuery();
				while(rs.next()){
					String sname = rs.getString(4);
					String sgender = rs.getString(2);
					String sinstitute = rs.getString(3);

					StudentBean getOne = new StudentBean(sid, sname, sgender, sinstitute);
					targetStudents.add(getOne);
				}
			}
		} catch (Exception exc) {
			
		} finally {
			conn.close();
		}

		return targetStudents;
	}
}
